package nl.mad.drib.util.sqlrunner;

import static org.apache.commons.lang.StringUtils.isEmpty;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import nl.mad.drib.util.JdbcUtils;

/**
 * Default implementation of the {@link SqlScriptRunner}. Uses an
 * {@link SqlReader} to retrieve statements, and then executes them
 * directly on the JDBC connection. After each statement has been
 * executed, the connection will be commited, and statements will
 * be closed.
 * 
 * @author Jeroen van Schagen
 * @since 04-05-2011
 */
public class DefaultSqlScriptRunner implements SqlScriptRunner {

    /**
     * {@inheritDoc}
     */
    @Override
    public void runScript(Reader scriptReader, Connection connection) {
        Statement statement = null;
        try {
            statement = connection.createStatement();
            executeStatements(scriptReader, statement);
            JdbcUtils.commitSafely(connection);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(statement);
        }
    }

    /**
     * Execute each statement descriped in our script.
     * @param scriptReader reads the content of our SQL script
     * @param statement used to perform statements on our database
     * @throws IOException whenever we could not continue reading
     * @throws SQLException whenever we could not execute a statement
     */
    private void executeStatements(Reader scriptReader, Statement statement) throws IOException, SQLException {
        SqlReader sqlReader = new SqlReader(scriptReader);
        while (true) {
            String sql = sqlReader.nextStatement();
            if (sqlReader.isDone()) break; // EOF reached
            try {
                statement.execute(sql);
            } catch (SQLException e) {
                // Include line number information to our exception
                throw new SQLException("Error during script execution at line " + sqlReader.getCurrentLineNumber(), e);
            }
        }
    }

    /**
     * Structured Query Language (SQL) statement reader. This reader recognizes
     * each line as a seperate statement, and skips comment statements.
     * 
     * @author Jeroen van Schagen
     * @since 04-05-2011
     */
    public static class SqlReader {
        private final BufferedReader bufferedReader;
        private boolean done = false;
        private int lineNo = 0;

        /**
         * Construct a new {@link SqlReader}.
         * @param reader the reader to wrap
         */
        public SqlReader(Reader reader) {
            bufferedReader = new BufferedReader(reader);
        }

        /**
         * Retrieve the next SQL statement.
         * @return next statement
         * @throws IOException whenever we could not read
         */
        public String nextStatement() throws IOException {
            if (done) {
                // Cannot continue after end of file
                return null;
            }
            String sql = bufferedReader.readLine();
            if (sql == null) {
                // End of file reached
                done = true;
            } else {
                lineNo++;
                // Remove leading and trailing whitespaces
                // This makes our statement easier to identify
                sql = sql.trim();
                if (isEmpty(sql) || isComments(sql)) {
                    // Skip comments and blank lines
                    return nextStatement();
                }
                sql = stripSemicolon(sql);
            }
            return sql;
        }

        /**
         * Determine if some SQL statement is comments.
         * @param sql statement to check
         * @return {@code true} if it is comments, else {@code false}
         */
        protected boolean isComments(String sql) {
            return sql.startsWith("--") || sql.startsWith("//") || sql.startsWith("/*");
        }

        /**
         * Strip the semicolon ';' of an SQL statement, if it has any.
         * @param sql statement to check
         * @return same statement, but without semicolon
         */
        private String stripSemicolon(String sql) {
            String result = sql;
            if (sql.endsWith(";")) {
                result = sql.substring(0, sql.length() - 1);
            }
            return result;
        }

        /**
         * Check if there are no more statements left.
         * @return {@code true} if no statements are left, else {@code false}
         */
        public boolean isDone() {
            return done;
        }

        /**
         * Retrieve the current line number.
         * @return current line number
         */
        public int getCurrentLineNumber() {
            return lineNo;
        }

    }

}
